package cn.edu.scau.cmi.tangqingning.tools;

import java.io.File;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.edu.scau.cmi.tangqingning.model.Group;
import cn.edu.scau.cmi.tangqingning.model.Student;
import jxl.Sheet;
import jxl.Workbook;

/**
 * 该类是用来获取excel表格或者数据库的数据的。
 * @author 唐庆宁
 * @author 谭祖杰
 */
public class GetData {
	/**
	 * 查询stuperinfo表中所有的数据
	 * @param obj Object，传入一个对象
	 * @return  list
	 */
	@SuppressWarnings("unchecked")
	public static List getAllByDb(Object obj) {
		List list = new ArrayList();
		try {
			DbConnection db = new DbConnection();
			String sql = "select * from stuperinfo";
			ResultSet rs = db.Search(sql, null);
			while (rs.next()) {
				Long stId = rs.getLong("stId");
				String stName = rs.getString("stName");
				String stSex = rs.getString("stSex");
				String stAge = rs.getString("stAge");
				Long stTel = Long.parseLong((rs.getString("stTel")));
				String stDept = rs.getString("stDept");
				String stAddress = rs.getString("stAddress");
				String stgroup = rs.getString("stgroup");
				Long password = rs.getLong("password");
				obj = new Student(stId, stName, stSex, stAge, stTel, stDept, stAddress, stgroup, password);
				list.add(obj);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * 查询指定目录中电子表格中所有的学生个人信息数据
	 * @param file String，文件完整路径 
	 * @param stu Student
	 * @return list
	 */
	@SuppressWarnings("unchecked")
	public static List getAllStuPerInfoByExcel(String file, Student stu) {
		List list = new ArrayList();
		try {
			Workbook rwb = Workbook.getWorkbook(new File(file));
			Sheet rs = rwb.getSheet("Sheet1");
			int clos = rs.getColumns();// 得到所有的列
			int rows = rs.getRows();// 得到所有的行

			System.out.println("clos:" + clos + "; rows:" + rows);
			for (int i = 1; i < rows; i++) {
				for (int j = 0; j < clos; j++) {
					// 第一个是列数，第二个是行数
					String stId = rs.getCell(j++, i).getContents();
					String stName = rs.getCell(j++, i).getContents();
					String stSex = rs.getCell(j++, i).getContents();
					String stAge = rs.getCell(j++, i).getContents();
					String stTel = rs.getCell(j++, i).getContents();
					String stDept = rs.getCell(j++, i).getContents();
					String stAddress = rs.getCell(j++, i).getContents();
					String stgroup = rs.getCell(j++, i).getContents();
					String password = rs.getCell(j++, i).getContents();
					stu = new Student(Long.parseLong(stId), stName, stSex, stAge, Long.parseLong(stTel), stDept,
							stAddress, stgroup, Long.parseLong(password));
					list.add(stu);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;

	}

	/**
	 * 查询指定目录中电子表格中所有的项目数据
	 * @param realPath String，文件完整路径 
	 * @param group1 group1，传入的对象
	 * @return list
	 */
	@SuppressWarnings("unchecked")
	public static List getAllProjectDataByExcel(String realPath, Group group1) {
		List list = new ArrayList();
		try {
			Workbook rwb = Workbook.getWorkbook(new File(realPath));
			Sheet rs = rwb.getSheet("Sheet1");// 或者rwb.getSheet(0)
			int clos = rs.getColumns();// 得到所有的列
			int rows = rs.getRows();// 得到所有的行

			System.out.println("clos:" + clos + "; rows:" + rows);
			for (int i = 1; i < rows; i++) {
				for (int j = 0; j < clos; j++) {
					// 第一个是列数，第二个是行数
					int groupNumber = Integer.parseInt(rs.getCell(j++, i).getContents());// 默认最左边编号也算一列，
					// 所以这里得j++
					String proIntroduction = rs.getCell(j++, i).getContents();
					String proRequirement = rs.getCell(j++, i).getContents();
					String proDivision = rs.getCell(j++, i).getContents();
					int numberOfEvaluater = Integer.parseInt(rs.getCell(j++, i).getContents());
					double functionScore = Double.parseDouble(rs.getCell(j++, i).getContents());
					double interfaceScore = Double.parseDouble(rs.getCell(j++, i).getContents());
					double innovationScore = Double.parseDouble(rs.getCell(j++, i).getContents());
					double oralDefenseScore = Double.parseDouble(rs.getCell(j++, i).getContents());
					double averageScore = Double.parseDouble(rs.getCell(j++, i).getContents());

					System.out.println("groupNumber:" + groupNumber + " " + "proIntroduction:" + proIntroduction + " "
							+ "proRequirement:" + proRequirement + " " + "proDivision:" + proDivision + " "
							+ "numberOfEvaluater:" + numberOfEvaluater + " " + "functionScore:" + functionScore + " "
							+ "interfaceScore:" + interfaceScore + " " + "innovationScore:" + innovationScore + " "
							+ "oralDefenseScore:" + oralDefenseScore + " " + "averageScore:" + averageScore);
					group1 = new Group(groupNumber, proIntroduction, proRequirement, proDivision, numberOfEvaluater,
							functionScore, interfaceScore, innovationScore, oralDefenseScore, averageScore);
					list.add(group1);
				}
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;

	}

	/**
	 * 遍历stuperinfo表，通过stId判断是否存在
	 * @param stId String
	 * @return true; false
	 */
	public static boolean isExist(String stId) {
		try {
			DbConnection db = new DbConnection();
			ResultSet rs = db.Search("select * from stuperinfo where stId=?", new String[] { stId });
			if (rs.next()) {
				return true; // 已存在
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	/**
	 * 遍历projectdata表，通过stId判断是否存在
	 * @param groupNumber String
	 * @return true; false
	 */
	public static boolean isExist1(String groupNumber) {
		try {
			DbConnection db = new DbConnection();
			ResultSet rs = db.Search("select * from projectdata where groupNumber=?", new String[] { groupNumber });
			if (rs.next()) {
				return true; // 已存在
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

}